/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import dto.userDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.acessoBanco;

/**
 *
 * @author emidio
 */
public class userDAO extends acessoBanco{
    
    public String addUser(userDTO user) throws Exception{
        if(user==null){
            return "cadastroErro";
        }
        try{
            String sql ="INSERT INTO cadastro.tb_usuario (LOGIN,SENHA,NOME) VALUES (?,?,?)";
            PreparedStatement ps = super.getPrepareStatemen(sql);
            
            ps.setString(1, user.getLogin());
            ps.setString(2, user.getSenha());
            ps.setString(3, user.getNome());
            ps.executeUpdate();
        }catch(Exception e){
            return "cadastroErro";
        }
        return "refresh";
    }
    public String updateUser(userDTO user) throws Exception {
        try{
            String sql = "UPDATE cadastro.tb_usuario SET nome = ?, login = ?,senha = ? WHERE id_usuario=?";

            PreparedStatement ps = getPrepareStatemen(sql);
            
            ps.setString(1, user.getNome());
            ps.setString(2, user.getLogin());
            ps.setString(3, user.getSenha());
            ps.setInt(4, user.getId());

            ps.executeUpdate();
        }catch(Exception e){
            return "updateErro";
        }
        return "updateSucesso";
    }

    public String deleteUser(userDTO user) throws Exception{
        try{
            String sql = "DELETE FROM cadastro.tb_usuario WHERE id_usuario = ? ";
            PreparedStatement ps = super.getPrepareStatemen(sql);

            ps.setInt(1, user.getId());
            
            ps.executeUpdate();
        }catch(Exception e){
            return "deleteErro";
        }
            return "refresh";
    }
    public userDTO getUserById(userDTO user) throws Exception{
        try{        
            String sql ="SELECT * FROM cadastro.tb_usuario WHERE id_usuario = ?";
            PreparedStatement ps = super.getPrepareStatemen(sql);
            ps.setInt(1, user.getId());
            ResultSet rs = ps.executeQuery();
            if(!rs.next()){
                return null;
            }
            user.setId(rs.getInt(1));
            user.setLogin(rs.getString(2));
            user.setSenha(rs.getString(3));
            user.setNome(rs.getString(4));
        }catch(Exception e){
            throw new Exception("Erro: "+e);
        }
        return user;
    }
    
    public ArrayList<userDTO> getTodosUsuarios() throws Exception{
        ResultSet rs = null;
        String sql ="SELECT * FROM cadastro.tb_usuario";
        ArrayList<userDTO> users = new ArrayList<userDTO>();
        try{
            PreparedStatement ps = super.getPrepareStatemen(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                users.add(new userDTO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4)));
            }
        }catch(Exception e){
            throw  new Exception("Erro: "+e);
        }
        return users;
    }
}
